package edu.tms.dao;
import java.util.List;
import java.util.Map;

import edu.tms.bean.Attendance;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 学生考勤表dao
 * @author Administrator
 *
 */
@SuppressWarnings("rawtypes")
public class AttendanceDaoImp implements CommnonDao{
	/**
	 * 保存内容
	 * @param value
	 * @return 
	 * @throws Exception
	 */
	@Override
	public long save(Object value) throws Exception {
		if(value instanceof Attendance){
			Attendance att = (Attendance)value;
			String sql="insert into t_attendance(course_attendance_id,stu_id,create_time,arrive_late_state,leave_early_state,";
			sql+="beg_off_state)VALUES (?,?,?,?,?,?);";
			JdbcUtil.update(sql,
				att.getCourseAttendanceId(),
				att.getStuId(),
				att.getCreateTime(),
				att.getArriveLateState(),
				att.getLeaveEarlyState(),
				att.getBegOffState()
			);
		}
		return 0;
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Object value) throws Exception {
		if(value instanceof Attendance){
			Attendance att = (Attendance)value;
			String sql="update t_attendance set stu_id=? ,create_time=?,arrive_late_state=? ,leave_early_state=?,"; 
			sql+="beg_off_state=? where attendance_id=?";
			JdbcUtil.update(sql, 
				att.getStuId(),
				att.getCreateTime(),
				att.getArriveLateState(),
				att.getLeaveEarlyState(),
				att.getBegOffState(),
				att.getAttendanceId()
			);
		}
		return 0;
	}
	/**
	 * 删除内容,关联表
	 * 学生表t_student,课程考勤表 t_course_attendance
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Object value) throws Exception {
			String sql="delete from t_attendance where attendance_id= ?";
			Integer id=(Integer) value;
			return JdbcUtil.update(sql,
				id	
			);
	}
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Attendance> findAll(int pageNo, int pageSize) throws Exception {
		String sql="SELECT st.stu_name,co.course_name,ca.course_term,ca.course_week,ca.course_day,ca.course_time "
			 +",att.arrive_late_state,att.leave_early_state,att.beg_off_state "
			 +"FROM t_student AS st ,t_course AS co ,t_course_attendance AS ca ,t_attendance AS att "
			 +"WHERE st.stu_id = att.stu_id AND co.course_id = ca.course_id "
			 + "AND ca.course_attendance_id = att.attendance_id and st.stu_squad_id = ?"
			+"limit ?,?";
		return JdbcUtil.query(Attendance.class, sql, (pageNo-1)*pageSize,pageSize);
	}
	
	/**
	 * 根据小组查询
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Attendance> findBySquad(int squadId,int pageNo, int pageSize) throws Exception {
		String sql="SELECT st.stu_name , co.course_name , ca.course_term,ca.course_week,ca.course_day,ca.course_time "
				+",att.arrive_late_state,att.leave_early_state,att.beg_off_state "
				+"FROM t_attendance AS att , t_course_attendance AS ca , t_course AS co , t_student AS st "
				+"WHERE co.course_id = ca.course_id AND ca.course_attendance_id = att.course_attendance_id "
				+"AND st.stu_id = att.stu_id "
				+"AND att.stu_id IN (SELECT st.stu_id FROM t_student AS st WHERE st.stu_squad_id = ?) limit ?,? ";
		return JdbcUtil.query(Attendance.class, sql, squadId, (pageNo-1)*pageSize,pageSize);
	}
	/**
	 * 查找单一实体
	 * @param Key
	 * @throws Exception
	 */
	@Override
	public Attendance findByKey(Object Key) throws Exception {
		return null;
	}

	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<Attendance> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT st.stu_name,co.course_name,ca.course_term,ca.course_week,ca.course_day,ca.course_time "
			 +",att.arrive_late_state,att.leave_early_state,att.beg_off_state "
			 +"FROM t_student AS st ,t_course AS co ,t_course_attendance AS ca ,t_attendance AS att "
			 +"WHERE st.stu_id = att.stu_id AND co.course_id = ca.course_id AND ca.course_attendance_id = att.attendance_id "
			 +"AND (co.course_name = ? or st.stu_name = ?) limit ?,?";
		return JdbcUtil.query(Attendance.class, sql, key,key,pageSize*(pageNo-1),pageSize);
	}
	
	@SuppressWarnings("unchecked")
	public List<Attendance> findAll(String key, int squadId, int pageNo, int pageSize) throws Exception {
		String sql="SELECT st.stu_name , co.course_name , ca.course_term,ca.course_week,ca.course_day,ca.course_time "
				+",att.arrive_late_state,att.leave_early_state,att.beg_off_state "
				+"FROM t_attendance AS att , t_course_attendance AS ca , t_course AS co , t_student AS st "
				+"WHERE co.course_id = ca.course_id AND ca.course_attendance_id = att.course_attendance_id "
				+"AND st.stu_id = att.stu_id "
				+"AND (co.course_name = ? or st.stu_name = ?) and st.stu_squad_id = ? limit ?,?";
		return JdbcUtil.query(Attendance.class, sql, key,key,squadId,pageSize*(pageNo-1),pageSize);
	}
	
	public long getPages(int pageSize) throws Exception{
		String sql="SELECT count(*) "
			 +"FROM t_student AS st ,t_course AS co ,t_course_attendance AS ca ,t_attendance AS att "
			 +"WHERE st.stu_id = att.stu_id AND co.course_id = ca.course_id "
			 + "AND ca.course_attendance_id = att.attendance_id ";
		long counts = (long) JdbcUtil.uniqueValue(sql,1);
		return counts%pageSize==0?counts/pageSize:counts/pageSize+1;
	}
	
	//根据课程id查询显示    分页显示
	@SuppressWarnings("unchecked")
	public List<Attendance> findByCoursId(int id,int pageNo,int pageSize) throws Exception{
		String sql="SELECT squad_name,squad_leader";
		 sql+=",stu_name,arrive_late_state";
		  sql+=",leave_early_state,beg_off_state,course_name,c.course_id";
		  sql+=" FROM t_student AS s LEFT JOIN t_attendance AS a ON a.stu_id=s.stu_id";
		  sql+=" LEFT JOIN t_squad AS q ON q.squad_id=s.stu_squad_id";
		  sql+=" LEFT JOIN t_course_attendance AS ca ON ca.course_attendance_id=a.course_attendance_id";
		  sql+=" LEFT JOIN t_course AS c ON c.course_id=ca.course_id";
		  sql+=" WHERE c.course_id=? limit ?,?;";
		List<Attendance> k=JdbcUtil.query(Attendance.class, sql, id,pageNo,pageSize);
		return k;
	}
	//根据课程ID查出所学生考勤的数据条数
	@SuppressWarnings("unchecked")
	public int findByCoursIds(int id,int pageSize) throws Exception{
		String sql="SELECT squad_name,squad_leader";
		 sql+=",stu_name,arrive_late_state";
		  sql+=",leave_early_state,beg_off_state,course_name,c.course_id";
		  sql+=" FROM t_student AS s LEFT JOIN t_attendance AS a ON a.stu_id=s.stu_id";
		  sql+=" LEFT JOIN t_squad AS q ON q.squad_id=s.stu_squad_id";
		  sql+=" LEFT JOIN t_course_attendance AS ca ON ca.course_attendance_id=a.course_attendance_id";
		  sql+=" LEFT JOIN t_course AS c ON c.course_id=ca.course_id";
		  sql+=" WHERE c.course_id=?;";
		 List<Attendance> k=JdbcUtil.query(Attendance.class, sql, id);
		 int page=k.size();
		 int pages=page%pageSize==0?page/pageSize:page/pageSize+1;//总页数
		return pages;
	}
	//根据学生id和学生姓名来查询
	@SuppressWarnings("unchecked")
	public List<Attendance> finByIdName(int id,Object key) throws Exception{
		String sql="SELECT squad_name,squad_leader";
		 sql+=",stu_name,arrive_late_state";
		  sql+=",leave_early_state,beg_off_state,course_name,c.course_id";
		  sql+=" FROM t_student AS s LEFT JOIN t_attendance AS a ON a.stu_id=s.stu_id";
		  sql+=" LEFT JOIN t_squad AS q ON q.squad_id=s.stu_squad_id";
		  sql+=" LEFT JOIN t_course_attendance AS ca ON ca.course_attendance_id=a.course_attendance_id";
		  sql+=" LEFT JOIN t_course AS c ON c.course_id=ca.course_id";
		  sql+=" WHERE c.course_id=? AND (s.stu_name=? OR s.stu_id=?);";
		  List<Attendance> kao=JdbcUtil.query(Attendance.class, sql,id, key,key);
		  return kao;
	}

}
